
#include<time.h>
#include <stdio.h>
#include <mysql.h>
#include<unistd.h>
#include <string.h>



int truenum;   //记录答对题目个数
int denglu(MYSQL db);
int zhuce(MYSQL db);
int test_system(MYSQL db,char *name);
int myhist(MYSQL db,char *name);
int exam(MYSQL db ,char *name);
int changepass(MYSQL db,char *name);

int rootcal(MYSQL db);
int myinsert(MYSQL db,unsigned int num_fields,MYSQL_FIELD *fields,char *table);
int mydelete(MYSQL db,char *table);
int myselect(MYSQL db,char *table,unsigned int num_fields);
int myupdate(MYSQL db,char *table);
int show(MYSQL db,unsigned int num_fields);
int operate(MYSQL *db,char *sql);



int denglu(MYSQL db)
{
    int jingao = 0;
    while(1)
    {
        if(jingao == 3) return 3;
        printf("请输入用户名和密码:");
        char name[128] = {0},pass[128] = {0};
        while(scanf("%s%s",name,pass) !=2)
        {
            printf("输入有误,请重新输入 ！\n");
            getchar();
        }
        char sql[128] = {0};
        sprintf(sql,"select * from user where name = '%s';",name);
        int ret = mysql_real_query(&db,sql,strlen(sql));
        if(ret < 0)
        {
            perror("mysql exec query error");
            continue;
        }
        if(mysql_errno(&db))
        {
            printf("error: %s\n",mysql_error(&db));
            continue;
        }
        MYSQL_RES *result = mysql_store_result(&db);
        if(NULL == result)
        {
            perror("MYSQL_RES error:");
            continue;
        }
        MYSQL_ROW row = mysql_fetch_row(result);
        if(NULL != row)
        {
            if(!strcmp(pass,row[2]))   //比较密码
            {
            printf("登录成功\n");
                if(!strcmp(name,"root"))  rootcal(db);
             else exam(db,name);   //进入考试系统
            break;
            }
            else{
                printf("用户名或密码有误,回车继续！\n");
                jingao++;

            }
        }
        else{
            printf("用户名不存在,重新登录\n");
            jingao ++;
        }
    }
    return 0;
}
int exam(MYSQL db,char *name)
{
    while(1)
    {
        system("clear");
        printf("**************在线答题系统**************\n");
        printf("**************1,开始答题****************\n");
        printf("**************2,查看历史成绩************\n");
        printf("**************3,修改密码****************\n");
        printf("**************4,返回登录界面************\n");
        printf("**************0,退出********************\n");
        int a = 0;
        while(scanf("%d",&a) != 1)
        {
            getchar();
            printf("**输入有误,重新输入***\n");
            sleep(0.5);
            bzero(&a,sizeof(a));
        }
        if(a == 1)   test_system(db,name);
        else if(a == 2)  myhist(db,name);
        else if(a == 3)  changepass(db,name);
        else  return 0;

    }
    return 0;
}
int changepass(MYSQL db,char *name)
{
    char sql[128] ={0};
    printf("请输入新密码:");
    char pass[128] ={0};
    getchar();
    fgets(pass,sizeof(pass),stdin);
    pass[strlen(pass)-1] = '\0' ;
    sprintf(sql,"update user set pass = '%s'where name = '%s';",pass,name);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return 0;
    }

    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return 0;
    }
    printf("\033[33m密码更改成功\033[0m**回车退出！！\n");
    getchar();
}
int myhist(MYSQL db,char *name)
{
    char sql[128] ={0};
    sprintf(sql,"select * from scores where name ='%s';",name);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return 0;
    }

    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return 0;
    }
    MYSQL_RES *result = mysql_store_result(&db);
    if(NULL == result)
    {
        perror("MYSQL_RES error:");
        return 0;
    }
    while(1)
    {
        MYSQL_ROW row = mysql_fetch_row(result);
        if(NULL == row)  break;
        printf("name:  \033[33m%s\33[0m  , score:  \33[33m%s\033[0m, time: \033[33m%s\033[0m\n",name,row[2],row[3]);
    }
    printf("回车退出\n");
    getchar();
    getchar();
    return 0;
}



int zhuce(MYSQL db)
{
    system("clear");
    while(1)
    {
        printf("请输入用户名和密码:");
        char name[128] = {0},pass[128] = {0};
        while(scanf("%s%s",name,pass) !=2)
        {
            printf("输入有误,请重新输入 ！\n");
            getchar();
        }
        char sql[128] = {0};
        sprintf(sql,"select * from user where name = '%s';",name);
        int ret = mysql_real_query(&db,sql,strlen(sql));
        if(ret < 0)
        {
            perror("mysql exec query error");
            continue;
        }
        if(mysql_errno(&db))
        {
            printf("error: %s\n",mysql_error(&db));
            continue;
        }
        MYSQL_RES *result = mysql_store_result(&db);
        if(NULL == result)
        {
            perror("MYSQL_RES error:");
            continue;
        }
        MYSQL_ROW row = mysql_fetch_row(result);
        if(NULL != row)
        {
            printf("用户名已存在\n");
        }
        else
        {
            sprintf(sql,"insert into user values(0,'%s','%s');",name,pass);
            ret = mysql_real_query(&db,sql,strlen(sql));
            if(ret < 0)
            {
                perror("mysql_real_query error:");
                continue;
            }
            if(mysql_errno(&db))
            {
                printf("error: %s\n",mysql_error(&db));
                continue;
            }
            printf("注册成功\n");
            mysql_free_result(result);
            sleep(1.7);
            return 0;
        }
    }
    return 0;
}


int test_system(MYSQL db,char *name)
{
    char sql[128] = {0};
    srand(time(NULL));
    int num = 20;
    int a[20] = {0};
    int sl =1;
    int flag = 0;
    truenum = 0;
    //char name[32] ={0};
    a[0] = rand()%99+1;    //产生20个随机数
    while(1)
    {
        int randd = rand()%139+1;
        for(int i =0;i < sl;i++)
        {
            if(randd == a[i])
            {
                flag =1;
                break;
            }
        }
        if(!flag)
        {
            a[sl] = randd;
            sl ++;
        }
        else flag = 0;
        if(sl == 20)
        {
            break;
        }
    }
    system("clear");
    printf("接下来系统会随机在题库中抽取20道题,答题期间可以退出,且不计入成绩,输入(\033[33mq\033[0m)退出。\033[0m，\033[32m回车\033[0m确认开始答题：\n");
    getchar();
    getchar();
    while(1)
    {
        sprintf(sql,"select * from test where id = %d;",a[20-num]);
        //mysql_real_query(&db,"set question utf8",17);
        int ret = mysql_real_query(&db,sql,strlen(sql));
        if(ret < 0)
        {
            perror("mysql exec query error");
            continue;
        }
        if(mysql_errno(&db))
        {
            printf("error: %s\n",mysql_error(&db));
            continue;
        }
        MYSQL_RES *result = mysql_store_result(&db);
        if(NULL == result)
        {
            perror("MYSQL_RES error:");
            continue;
        }
        MYSQL_ROW row = mysql_fetch_row(result);
        printf("\033[34m%d,\033[0m",21-num);
        for(int i = 0;i < 5;i ++)
        {
            if(i == 0)   printf("\033[34m%s\033[0m\n",row[i+1]);
            else         printf("\033[36m%s\033[0m\n",row[i+1]);
            //printf("\n");
        }
        printf("请选择:");
        char sct[128] = {0};
        
        fgets(sct,sizeof(sct),stdin);
        sct[strlen(sct)-1] ='\0';
        if(!strcmp(sct,"q")) return 0;
        else if(!strcmp(sct,row[6]))
        {
            printf("回答正确\n");
            truenum ++;
        }
        else if(strcmp(sct,"A") && strcmp(sct,"B") &&strcmp(sct,"C")&&strcmp(sct,"D"))
        {
            printf("选项不存在,请积极答题!\n");
            num ++;
        }
        else if(strcmp(sct,row[6]))
        {
            printf("回答错误,");
            printf("正确答案为 \033[31m%s\033[0m\n",row[6]);
        }
        mysql_free_result(result);
        num--;
        if(num == 0) break;
    }
    printf("答对%d个，成绩为:\033[33m%d\033[0m \n",truenum,truenum*5);
    sprintf(sql,"insert into scores values(0,'%s',%d,CURRENT_TIMESTAMP);",name,truenum*5);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
    }
    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
    }

    printf("***答题结束,回车退出****\n");

    getchar();
    
    return 0;
}

int rootcal(MYSQL db)
{
    char *table = "test";
    char sqlss[128] ={0};
    sprintf(sqlss,"select * from test;");
    int ret = mysql_real_query(&db,sqlss,strlen(sqlss));
	if(ret < 0)
	{
		perror("mysql exec query error");
		return -1;
	}
    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return -1;
    }



    MYSQL_RES *result = mysql_store_result(&db);
    MYSQL_FIELD *fields;
    unsigned int num_fields;
    fields = mysql_fetch_fields(result);
    num_fields = mysql_num_fields(result);
    printf("表头为:");
    for(int i = 0;i < num_fields; i++)
    {
        printf("%s ",fields[i].name);
    }
    printf("\n");

    while(1)
    {
        system("clear");
        printf("\033[33m********************************\n");
        printf("*           1,增加题库         *\n");
        printf("*           2,删除题库         *\n");
        printf("*           3,修改题库         *\n");
        printf("*           4,查询题目         *\n");
        printf("*           5,显示题库         *\n");
        printf("*           0,退出             *\n");
        printf("********************************\033[0m\n");
        int a = 0;
        int ret = scanf("%d",&a);
        if(ret != 1)
        {
            printf("输入有误 ！！\n");
            getchar();
            bzero(&a,sizeof(a));
            continue;
        }
        //a =getchar();
        if(a == 1)
        {
            myinsert(db,num_fields,fields,table);
        }
        else if(a ==2)
        {
            mydelete(db,table);
        }
        else if(a == 3)
        {
            myupdate(db,table);
        }
        else if(a == 4)
        {
            myselect(db,table,num_fields);
        }
        else if(a==5)
        {
            show(db,num_fields);
        }
        else if(a == 0) break;
        else{
            printf("输入有误，重新输入\n");
            bzero(&a,sizeof(a));
            getchar();
            continue;
        }

    }
    mysql_free_result(result);
}
int myinsert(MYSQL db,unsigned int num_fields,MYSQL_FIELD *fields,char *table)
{
    system("clear");
    printf("***********插入数据模块**********\n");
        char b[16][1024] = {0};
    getchar();
    for(int i = 1;i <num_fields;i++)
    {
        printf("请输入%s:",fields[i].name);
        fgets(b[i-1],sizeof(b[i-1]),stdin);
        b[i-1][strlen(b[i-1])-1] = '\0';
    }
    char sql[128] ={0};
    sprintf(sql,"insert into %s values(0,'%s','A.%s','B.%s','C.%s','D.%s','%s');",table,b[0],b[1],b[2],b[3],b[4],b[5]);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return -1;
    }
    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return -1;
    }
    printf("插入成功\n");
    getchar();

    return 0;
}
int mydelete(MYSQL db,char *table)
{
    system("clear");
    printf("************删除数据模块************\n");
    printf("请输入要删除的题目id:");
    char a[32] = {0};
    char sql[128] ={0};
    scanf("%s",a);


    sprintf(sql,"select * from %s where id = %s;",table,a);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return -1;
    }
    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return -1;
    }
    MYSQL_RES *result = mysql_store_result(&db);
    MYSQL_ROW row = mysql_fetch_row(result);
    if(row <= 0)
    {
        printf("无此题id\n");
        getchar();
        getchar();
        return 0;
    }




    sprintf(sql,"delete from %s where id = %s;",table,a);
    ret = operate(&db,sql);
    if( ret == 0)
    {
        printf("成功删除\n");
        sprintf(sql,"alter table %s AUTO_INCREMENT = 1;",table);
        operate(&db,sql);
    }
    getchar();
    getchar();
    return 0;
}
int myupdate(MYSQL db,char *table)
{
    system("clear");
    printf("************修改模块************\n");
    printf("请输入你要修改的题目id:");
    char id[32] = {0};
     char sql[1024]={0};
    getchar();
    fgets(id,sizeof(id),stdin);

    /*判断有无此题ID*/
    sprintf(sql,"select * from %s where id = %s;",table,id);
    int ret = mysql_real_query(&db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return -1;
    }
    if(mysql_errno(&db))
    {
        printf("error: %s\n",mysql_error(&db));
        return -1;
    }
    MYSQL_RES *result = mysql_store_result(&db);
    MYSQL_ROW row = mysql_fetch_row(result);
    if(row <= 0)
    {
        printf("无此题id\n");
        getchar();
        getchar();
        return 0;
    }
    printf("***请输入新的(question/A/B/C/D/answer)***\n");
    char ques[1024] = {0};
    char a[512] ={0};
    char b[512] ={0};
    char c[512] ={0};
    char d[512] ={0};
    char ans[32] ={0};
    printf("请输入问题:");
    fgets(ques,sizeof(ques),stdin);
    ques[strlen(ques-1)] = '\0';

    printf("请输入A选项:");
    fgets(a,sizeof(a),stdin);
    a[strlen(a)-1] = '\0';

    printf("请输入B选项:");
    fgets(b,sizeof(b),stdin);
    b[strlen(b)-1] = '\0';

    printf("请输入C选项:");
    fgets(c,sizeof(c),stdin);
    c[strlen(c)-1] = '\0';

    printf("请输入D选项:");
    fgets(d,sizeof(d),stdin);
    d[strlen(d)-1] = '\0';

    printf("请输入答案:");
    fgets(ans,sizeof(ans),stdin);
    ans[strlen(ans)-1] = '\0';

    sprintf(sql,"update %s set question = '%s' where id = %s;",table,ques,id);
    ret = operate(&db,sql);
    if(ret < 0)
    {
        return 0;
    }
    sprintf(sql,"update %s set A = '%s' where id = %s;",table,a,id);
    ret = operate(&db,sql);
    if(ret < 0)
    {
        return 0;
    }
    sprintf(sql,"update %s set B = '%s' where id = %s;",table,b,id);
    ret = operate(&db,sql);
    if(ret < 0)
    {
        return 0;
    }
    sprintf(sql,"update %s set C = '%s' where id = %s;",table,c,id);
    ret = operate(&db,sql);
    if(ret < 0)
    {
        return 0;
    }
    sprintf(sql,"update %s set D = '%s' where id = %s;",table,d,id);
    ret = operate(&db,sql);
    if(ret < 0)
    {
        return 0;
    }

    sprintf(sql,"update %s set answer = '%s' where id = %s;",table,ans,id);
    ret = operate(&db,sql);
    if(ret == 0)
    {
        printf("更新成功\n");
    }
    getchar();
    return 0;
}
int operate(MYSQL *db,char *sql)  //sql语句执行函数
{
    int ret = mysql_real_query(db,sql,strlen(sql));
    if(ret < 0)
    {
        perror("mysql exec query error");
        return -1;
    }
    if(mysql_errno(db))
    {
        printf("error: %s\n",mysql_error(db));
        return -1;
    }
    return 0;
}

int myselect(MYSQL db,char *table,unsigned int num_fields)
{
    getchar();
    system("clear");
    printf("***********查询数据模块************\n");
    printf("你要查询的题目(id):");
    char c[32] ={0};
    scanf("%s",c);
    char sql[128]={0};
    sprintf(sql,"select * from %s where id = %s;",table,c);
    operate(&db,sql);   //sql语句执行
    MYSQL_RES *result = mysql_store_result(&db);
    int m = 0;
    while(1)
    {
        MYSQL_ROW row = mysql_fetch_row(result);
        if(row <= 0 && m==0)
        {
            printf("***没有该题***\n");
            break;
        }
        m++;
        if(row <= 0)  break;
        for(int k = 0;k<num_fields;k++)
        {
            printf("\033[33m%s\033[0m ",row[k]);
        }
        printf("\n");
    }
    printf("\n");
    getchar();
    getchar();
    return 0;
}
int show(MYSQL db,unsigned int num_fields)
{
    char c[128] ={0};
    sprintf(c,"select * from test;");
    operate(&db,c);
    MYSQL_RES *result = mysql_store_result(&db);
    while(1)
    {
        MYSQL_ROW row = mysql_fetch_row(result);
        if(row <= 0) break;
        for(int k = 0;k<num_fields;k++)
        {
            if(k == num_fields -1)  printf("\033[32m%s\033[0m ",row[k]);
            else                    printf("%s ",row[k]);
            if(k != 0) printf("\n");
            if(k == num_fields -2) printf("答案为: ");
        }
        printf("\n");
    }
    getchar();
    getchar();
    return 0;

}





int main(int argc, char *argv[])
{
	if(argc != 2)
	{
		printf("usage %s ip\n",argv[0]);
		return 0;
	}

	MYSQL  db, *pdb;
	pdb = mysql_init(&db);
	if(NULL == pdb)
	{
		perror("mysql init");
		return -1;
	}
	printf("&db = %p  pdb = %p \n",&db,pdb);
	char *host= argv[1];
	char *user = "usr";
	char *passwd = "123";
	char *mydb = "test";
    printf("连接中\n");
	pdb = mysql_real_connect(&db,host,user,passwd,mydb,0,0,0);
	if(NULL == pdb)
	{
		printf("connect error  user or pass error\n");
		return -1;
	}
/* 设置数据库默认字符集 */
        if ( mysql_set_character_set( &db, "utf8" ) ) {
            fprintf ( stderr , "错误, %s\n" , mysql_error( &db) ) ;
	}
	//printf("成功连接数据库\n");
    system("clear");
    while(1)
    {
        system("clear");
        printf("*******************************\n");
        printf("*          \033[33m1,登录\033[0m             *\n");
        printf("*          \033[33m2,注册\033[0m             *\n");
        printf("*          \033[33m0,退出\033[0m             *\n");
        printf("*******************************\n");

        char table[32] ={0};
        scanf("%s",table);
        if(!strcmp(table,"1"))
        {
            int ret = denglu(db);
            if(ret == 3)
            {
                printf("\033[36m三次机会已用完\033[0m\n");
                mysql_close(&db);
                return 0;
            }
            
        }
        else if(!strcmp(table,"2"))
        {
            zhuce(db);
        }
        else if(!strcmp(table,"0"))
        {
            break;
        }
        else{
            printf("输入有误\n");
        }

    }
    mysql_close(&db);

    return 0;
}

